﻿Imports System.Data
Imports System.Data.SqlClient
Public Class ContasPagarDAL
#Region "Incluir"
    Public Function InserirAlterar(ByVal ctp_codigo As Integer, ByVal ccc_codigo As Nullable(Of Integer), ByVal com_codigo As Nullable(Of Integer), ByVal ctp_dtcompra As Date,
                                   ByVal ctp_descricao As String, ByVal ctp_fornprestador As String, ByVal usucod As Integer) As Integer
        Dim cmd = New SqlCommand
        Dim codigo As Integer
        Try
            cmd = New SqlCommand
            cmd.CommandText = "execute SPContasPagar @ctp_codigo, @ccc_codigo,@com_codigo,@ctp_dtcompra,@ctp_descricao,@ctp_fornprestador,@usu_codigo"
            cmd.Parameters.Add(New SqlParameter("@ctp_codigo", SqlDbType.Int)).Value = ctp_codigo
            If (ccc_codigo.Value <> 0) Then
                cmd.Parameters.Add(New SqlParameter("@ccc_codigo", SqlDbType.Int)).Value = ccc_codigo
            Else
                cmd.Parameters.Add(New SqlParameter("@ccc_codigo", SqlDbType.Int)).Value = DBNull.Value
            End If
            If (com_codigo.Value <> 0) Then
                cmd.Parameters.Add(New SqlParameter("@com_codigo", SqlDbType.Int)).Value = com_codigo
            Else
                cmd.Parameters.Add(New SqlParameter("@com_codigo", SqlDbType.Int)).Value = DBNull.Value
            End If
            cmd.Parameters.Add(New SqlParameter("@ctp_dtcompra", SqlDbType.Date)).Value = ctp_dtcompra
            cmd.Parameters.Add(New SqlParameter("@ctp_descricao", SqlDbType.VarChar)).Value = ctp_descricao
            cmd.Parameters.Add(New SqlParameter("@ctp_fornprestador", SqlDbType.VarChar)).Value = ctp_fornprestador
            cmd.Parameters.Add(New SqlParameter("@usu_codigo", SqlDbType.Int)).Value = usucod
            codigo = objconexao.executacmdAI(cmd, "Ofi_ContasPagar")
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function
#End Region
#Region "Buscar"
    Function buscar(ByVal descricao As String, tipo As Integer) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            Select Case tipo
                Case 0    'por Fron/prest
                    cmd.CommandText = "select * from vw_FinContasPagar where  ctp_fornprestador COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao order by icp_datavencimento asc"
                    cmd.Parameters.Add("@descricao", SqlDbType.VarChar).Value = "%" & descricao & "%"
                Case 1 ' inicial
                    cmd.CommandText = "select * from vw_FinContasPagar where sta_codigo = 4 and icp_datavencimento= @descricao order by icp_datavencimento asc"
                    cmd.Parameters.Add("@descricao", SqlDbType.Date).Value = descricao
                Case 2     'por C.Custo
                    cmd.CommandText = "select * from vw_FinContasPagar where ccc_descricao COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao order by icp_datavencimento asc"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & descricao & "%"
                Case 3  'status
                    cmd.CommandText = "select * from vw_FinContasPagar where sta_descricao COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao order by icp_datavencimento asc"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & descricao & "%"
                Case 4
                    cmd.CommandText = "select * from vw_FinContasPagar where  ctp_fornprestador COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao order by icp_datavencimento asc"
                    cmd.Parameters.Add("@descricao", SqlDbType.VarChar).Value = "%" & descricao & "%"
                Case Else 'todos
                    cmd.CommandText = "select * from vw_FinContasPagar where sta_codigo = 4 order by icp_datavencimento asc" 'Contas pagar UC
            End Select
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function buscarP(ByVal descricao As String, tipo As Integer) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            Select Case tipo
                Case 0     'por codigo
                    cmd.CommandText = "select * from vw_FinPrestFornecedor where  for_codigo=@descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.Int)).Value = Val(descricao)
                Case 1 'por Razão
                    cmd.CommandText = "select * from vw_FinPrestFornecedor where for_razao COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & descricao & "%"
                Case 2     'por C.Custo
                    cmd.CommandText = "select * from vw_FinPrestFornecedor where ccc_descricao COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & descricao & "%"
                    'Case 3  'status
                    '    cmd.CommandText = "select * from vw_FinPrestFornecedor where sta_descricao COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao"
                    '    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & descricao & "%"
                Case Else
                    cmd.CommandText = "select * from vw_FinPrestFornecedor where sta_codigo < 3"
            End Select
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
#Region "Localiza Cód" 'localizacod
    Function localizacod(ByVal num As Integer) As DataTable
        Dim tab As DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from vw_FinContasPagar where ctp_codigo=@num" '15/09 foi redef para ctp_cod
            cmd.Parameters.Add(New SqlParameter("@num", SqlDbType.Int)).Value = num
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function localizaicp(ByVal num As Integer) As DataTable
        Dim tab As DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from vw_FinContasPagar where icp_codigo=@num" '15/09 new
            cmd.Parameters.Add(New SqlParameter("@num", SqlDbType.Int)).Value = num
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
#Region "Filtro Dt Vencimento"
    Function locadata(ByVal dtini As Date, ByVal dtfim As Date) As DataTable 'Filtro datas
        Dim tab As DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from vw_FinContasPagar where icp_datavencimento BETWEEN @dtini AND @dtfim"
            cmd.Parameters.Add(New SqlParameter("@dtini", SqlDbType.Date)).Value = Format(dtini, "yyyy/MM/dd")
            cmd.Parameters.Add(New SqlParameter("@dtfim", SqlDbType.Date)).Value = Format(dtfim, "yyyy/MM/dd")
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
#Region "desabilitado"
    'Case 5
    '               cmd.CommandText = "select * from vw_FinContasPagar where ctp_codigo=@descricao" '15/09 foi redef para ctp_cod
    '               cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.Int)).Value = descricao
#End Region

End Class

